package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import models.OutputJob;
import models.User;
import models.service;

import org.springframework.stereotype.Repository;


@Repository
@SuppressWarnings("unchecked")
public class SpringDAO extends BaseDAO {
	private static final String TABLE=" users ";
	private static final String FIELDS=" email,fname,lanme,mname,company,jobtitle,cellphone,userLevel,regDate ";
	private static final String SAVED_VALUES=" :email,:fname,:lanme,:mname,:company,:jobtitle,:cellphone,:userLevel,:regDate ";
	private static final String UPDATED_VALUES=" email=:email,fname=:fname,lanme=:lname,mname=:mname,company=:company,jobtitle=:jobtitle,cellphone=:cellphone,userLevel=:userLevel,regDate=:regDate ";
	
	private Connection conn=null;
	private PreparedStatement stmt=null;
	private User currentUser;

	//check if there are email exits already
	public boolean availID(String email){
		
		String sql="SELECT email FROM users WHERE email = '"+email+"'";
		try {
			Connection con = getDs().getConnection();
			stmt = con.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			int numOfUser = 0;
			while(rs.next())
			{
		        numOfUser++;
			}
			con.close();
			if(numOfUser == 0){
				return true;
			}
			else
				return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return false;
		
	}
	
	//get user information by email
	public User getUser(String email){
		String sql="SELECT * FROM users WHERE email = '"+email+"'";
		currentUser = new User();
		try {
			Connection con = getDs().getConnection();
			stmt = getDs().getConnection().prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next())
			{
				currentUser.setId(rs.getInt("id"));
				currentUser.setEmail(email);
				currentUser.setFname(rs.getString("fname"));
				currentUser.setLname(rs.getString("lname"));
				currentUser.setMname(rs.getString("mname"));
				currentUser.setCompany(rs.getString("company"));
				currentUser.setJobtitle(rs.getString("jobtitle"));
				currentUser.setCellphone(rs.getString("cellphone"));
				currentUser.setUserLevel(rs.getInt("userLevel"));
				currentUser.setRegDate(rs.getString("regDate"));
				
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return currentUser;
	}
	
	//save user info into database
	public int save(User transientInstance) throws SQLException {
		String sql="insert into users (email,fname,lname,mname,company,jobtitle,cellphone,userLevel,regDate) values (?,?,?,?,?,?,?,?,?)";
		Connection con = getDs().getConnection();
		stmt = con.prepareStatement(sql);
		stmt.setString(1, transientInstance.getEmail());
		stmt.setString(2, transientInstance.getFname());
		stmt.setString(3, transientInstance.getLname());
		stmt.setString(4, transientInstance.getMname());
		stmt.setString(5, transientInstance.getCompany());
		stmt.setString(6, transientInstance.getJobtitle());
		stmt.setString(7, transientInstance.getCellphone());
		stmt.setInt(8, transientInstance.getUserLevel());
		stmt.setString(9, transientInstance.getRegDate());
		
		stmt.executeUpdate();
		
		con.close();
		return 1;
	}
	
	//get job history for id
	public List<OutputJob> outputlist(int Id){
		
		String sql="SELECT appname, command, subDate, outURL,appID, srvURL FROM jobs WHERE userID = '"+Id+"'";
		List<OutputJob> jobs = new ArrayList<OutputJob>();
		try {
			Connection con = getDs().getConnection();
			stmt = con.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				OutputJob job = new OutputJob();
				job.setAppname(rs.getString("appname"));
				job.setCmd(rs.getString("command"));
				job.setDate(rs.getString("subDate"));
				job.setJobUrl(rs.getString("outURL"));
				job.setAppId(rs.getString("appID"));
				job.setServiceURL(rs.getString("srvURL"));
				jobs.add(job);
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jobs;
	}
	
	//save job into database
	public void savejobs(String job, int userid, String cmd,String appname, String date,String appid,String srvurl){
		String sql="insert into jobs (outURL, userID,appname,command,subDate,appID,srvURL) values (?,?,?,?,?,?,?)";
		try {
			Connection con = getDs().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, job);
			stmt.setInt(2, userid);
			stmt.setString(3, appname);
			stmt.setString(4, cmd);
			stmt.setString(5, date);
			stmt.setString(6, appid);
			stmt.setString(7, srvurl);
			stmt.executeUpdate();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//save service list into database
	public void saveSRV(List<service> srvs){		
		String sql="insert into services (srvname, srvurl, srvusage) values(?,?,?)";
		try {
				Connection con=getDs().getConnection();
				
				//clear table before re-saving it
				stmt = con.prepareStatement("delete from services");
				stmt.execute();
				 for(int i=0;i<srvs.size();i++){
					stmt = con.prepareStatement(sql);
					stmt.setString(1, srvs.get(i).getName());
					stmt.setString(2, srvs.get(i).getUrl());
					stmt.setString(3, srvs.get(i).getUsage());
					stmt.executeUpdate();
				 }
					con.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//get srvlist from database!
	public List<service> getSRV(){
		List<service> srvs = new ArrayList<service>(); 
		String sql="SELECT srvname, srvurl, srvusage FROM services";
		try {
			Connection con = getDs().getConnection();
			stmt = con.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
				service srv = new service(rs.getString("srvname"),rs.getString("srvurl"),rs.getString("srvusage"));
				srvs.add(srv);
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return srvs;
	}
	
	//set check--if srvlist is already saved into database
	public void setcheck(String check){
		String sql = "UPDATE UTIL set checks ='"+check+"'";
		try {
			Connection con = getDs().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.executeUpdate();
			con.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//set check--if srvlist is already saved into database	
	public String getcheck(){
		String sql="SELECT checks FROM UTIL";
		String check="";
		try {
			Connection con = getDs().getConnection();
			stmt = con.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			
			while(rs.next()){
				check = rs.getString("checks");
			}
			
			con.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return check;
	}
	
}
